<?php
require_once(dirname(__FILE__) . '/../../inc/config.inc.php');
date_default_timezone_set('PRC');
require_once(dirname(__FILE__) . '/../../../vendor/autoload.php');
// 使用 PHPSpreadsheet 命名空间
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;

$year=isset($_GET['year']) ? intval($_GET['year']) :  date('Y');


//获取每月 报修数量
$year_data = array();

for ($i = 1; $i <= 12; $i++) {    // 年

    if (intval($i) < 10) {
        $i = "0" . $i;
    }
    $firstday = $year."-" . $i . "-01";
    $lastday = date('Y-m-d', strtotime("$firstday +1 month -1 day"));
    $start = strtotime($firstday . " 00:00:00");
    $end = strtotime($lastday . " 23:59:59");

    $year_data[] = array('month' => $i,'firstday'=>$firstday,'lastday'=>$lastday, 'start' => $start, 'end' => $end);
}


//获取工程师列表
$manage_arr = [];
$dosql->Execute("SELECT * FROM  `#@__user`  WHERE `level`=3 and isreport=1  ORDER BY id asc", 8);
while ($row3 = $dosql->GetArray(8)) {
    $manage_arr[] = $row3;
}


$objPHPExcel = new Spreadsheet();// 操作第一个工作表
//新建第一个工作表
$sheetname = $objPHPExcel->getActiveSheet();

$styleThinBlackBorderOutline = array(
    'borders' => array(
        'allborders' => array( //设置全部边框
            'style' => Border::BORDER_THIN //粗的是thick
        ),

    ),
);


$cellKey = array(
    'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
    'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
    'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM',
    'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
);


//月报表 表列说明
$cellName = [
    'id' => array('序号', 'id', 6), //列名称，索引值，宽度
    'truename' => array('姓名', 'truename', 10), //列名称，索引值，宽度
];

foreach ($year_data as $key => $val) {
    $cellName[$val['month']] = [$val['month'] . "月", "m" . $val['month'], 12];
}
$cellName['total'] = ['合计', 'total', 12];
$cellName['remark'] = ['签字确认', 'remark', 12];
// 循环周
$user_week_fee = [];
$all_total_fee = 0; //本年所有人员的积分
$user_count = 0;
$item = [];
foreach ($manage_arr as $kkk => $userid) {




    //循环 人
    $nn = 1;  //人员序号
    $new_item=[];
    $new_item['id'] = $nn;
    $new_item['truename'] = $userid['truename'];
    $week_fee=0;
    foreach ($year_data as $key => $val) {
        $user_count++;

        $key_name = 'm' . $val['month'];
        $day_start = $val['start'];
        $day_end = $val['end'];
        $day_fee = 0;
        $uid = $userid['id'];
        $sql = "select op,fee from  `#@__fee_log` where manageid='$uid' and op_time>=$day_start and op_time<=$day_end ";
//        echo $userid['truename']."   ".$key_name."   ".$sql."<br>";
        //echo $sql;
        $dosql->Execute($sql);
        while ($row_fee = $dosql->GetArray()) {
            //var_dump($row_fee);
            //echo "rrr" . $row_fee['fee'] . "<br>";
            if ($row_fee['op'] == "add") {
                $day_fee += $row_fee['fee'];
            }
            if ($row_fee['op'] == "move") {
                $day_fee -= $row_fee['fee'];
            }
        }
        $new_item[$key_name] = $day_fee;
        $all_total_fee += $day_fee;
        $week_fee += $day_fee;
        $nn++;
    }
    $new_item['total'] = $week_fee;
    $new_item['remark'] = "";
    $item[] = $new_item;
}


//数组进行排序
//array_multisort(array_column($item, 'total'), SORT_DESC, $item);

//var_dump($cellName);

// 设置第一行
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);//垂直居中
$sheetname->mergeCells('A1:' . $cellKey[count($cellName) - 1] . '1');
//合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）
$sheetname->setCellValue('A1', "服务管理虚拟收入明细表");
$sheetname->getStyle('A1')->getFont()->setName('黑体')->setSize(14);
$sheetname->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheetname->getStyle('A1')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
$sheetname->getStyle('A1')->getAlignment()->setWrapText(true);

// 设置第二行
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);//垂直居中
$sheetname->mergeCells('A2:' . $cellKey[count($cellName) - 1] . '2');
//合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）
$sheetname->setCellValue('A2', "部门：项目工程部 ");

$sheetname->getRowDimension('1')->setRowHeight(33);
$sheetname->getStyle('A2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheetname->getStyle('A2')->getAlignment()->setWrapText(true);
$sheetname->getStyle('A2')->getFont()->setName('黑体')->setSize(12);
$sheetname->getRowDimension('2')->setRowHeight(27);

$topNumber = 3;//表头有几行占用


$cellName_num = count($cellName);
$lastCell = $cellKey[$cellName_num - 1]; //获取最后一行的 excel 字母


$i = 0;
//处理 表头
foreach ($cellName as $k => $v) {
    $sheetname->setCellValue($cellKey[$i] . $topNumber, $v[0]);//设置表头数据

    $sheetname->getColumnDimension($cellKey[$i])->setWidth($v[2]);//设置列宽度
    //添加边框
    $sheetname->getStyle($cellKey[$i] . $topNumber)->applyFromArray($styleThinBlackBorderOutline);
    //设置字体 为10号

    // 设置所有垂直居中
    $sheetname->getStyle($cellKey[$i] . $topNumber)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

    $sheetname->getRowDimension($topNumber)->setRowHeight(24);
    //设置字体 为10号 黑体
    $sheetname->getStyle($cellKey[$i] . $topNumber)->getFont()->setName('黑体')->setSize(12);

    // 居中
    $sheetname->getStyle($cellKey[$i] . $topNumber)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $i++;
}



//处理数据
foreach ($item as $k => $v) {
    // 当前行数值
    $now_line = $k + 1 + $topNumber;
    $ii = 0;
    foreach ($cellName as $k1 => $v1) {
        $sheetname->setCellValue($cellKey[$ii] . $now_line, $v[$v1[1]]);
        $sheetname->getStyle($cellKey[$cellName_num - 2] . $now_line)->getFont()->getColor()->setARGB(Color::COLOR_RED);// 设置文字颜色
        $ii++;
    }

    // 设置所有垂直居中
    $sheetname->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->getAlignment()
        ->setVertical(Alignment::VERTICAL_CENTER);
    // 居中
    $sheetname->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->getAlignment()
        ->setHorizontal(Alignment::HORIZONTAL_CENTER);
    //添加边框
    $sheetname->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->applyFromArray($styleThinBlackBorderOutline);
    $sheetname->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->getFont()->setName('黑体')->setSize(12);

    $sheetname->getRowDimension($now_line)->setRowHeight(27);


}

$sheetname->setTitle("2021月份报表");


//设置活动单指数到第一个表,所以Excel打开时，是第一个表
$objPHPExcel->setActiveSheetIndex(0);

$xlsTitle = '奖励列表';
ob_end_clean();
//清除缓冲区,避免乱码
header('Content-type: application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
header("Content-Disposition: attachment; filename=$xlsTitle.xls");
header('Cache-Control: max-age=0');
//header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
//header ('Cache-Control: cache, must-revalidate');
header('Pragma: public');
$objWriter = IOFactory::createWriter($objPHPExcel, 'Xls');
$objWriter->save('php://output');


exit
?>

